# -*- coding: utf-8 -*-
import re
import sqlite3
import requests
import time


def cleanhtml(html):
    pattern = '\s+|(&nbsp;)+'
    # pattern='\s+'
    html = re.sub(pattern, ' ', html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return html.replace('> <', '><')


def getfetch():
    auth_url = 'http://127.0.0.1:50003/auth_npedi'
    url = 'http://www.npedi.com/edi/ediweb/ingateInfoContent.jsp'
    auth = requests.get(auth_url, params={'site': 'npedi', 'action': 'remote'},
                        headers={'Accept': 'application/json'}).json()
    print auth
    cookies = dict(JSESSIONID=auth['cookie'])
    res = requests.get(url, cookies=cookies)
    trs = re.findall('<TR align="center">.*?</TR>', cleanhtml(res.text))
    print len(trs), trs[0]
    rows = []
    for tr in trs:
        tds = re.findall('<TD height="24">(.*?)</TD>', tr)
        rows.append(cleanval(tds))
    return rows


def cleanval(v):
    if type(v) == list or type(v) == tuple:
        return [None if x.strip() == '' else x.strip() for x in v]
    else:
        return None if v.strip() == '' else v.strip()


# ##########################################
# 主程序开始
# ##########################################
head = ('xh', 'zwcm', 'ywcm', 'cbundm', 'hc', 'jxmt', 'jxkssj', 'jxjzsh')

s = time.time()
rs = getfetch()
print 'fetch use second:', time.time() - s

s = time.time()
conn = sqlite3.connect('npedi.db3')
sql = "delete from ingateinfo"
conn.execute(sql)

for r in rs:
    cols = ','.join(head)
    values = ','.join(['?'] * len(head))
    sql = 'insert into ingateinfo (%s) values(%s)' % (cols, values)
    conn.execute(sql, r)
conn.commit()
conn.close()
print 'write db use second:', time.time() - s